package dao;

import po.Book;
import po.Order;
import po.OrderItem;
import po.User;
import utils.DBUtils;
import vo.OrderItemVo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class OrderItemDao {
    public void insert(OrderItem orderItem){
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        String sql ="insert into tb_order_item (product,price,count,order_id) values(?,?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,orderItem.getProduct());
            preparedStatement.setDouble(2,orderItem.getPrice());
            preparedStatement.setInt(3,orderItem.getCount());
            preparedStatement.setString(4,orderItem.getOrderId());
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
    }

    public ArrayList<OrderItemVo> selectByIdAssociatedBook(String orderId){
        ArrayList<OrderItemVo> list = new ArrayList<OrderItemVo>();
        Book book = null;
        OrderItem orderItem = null;
        Order order = null;
        OrderItemVo orderItemVo = null;
        Connection connection = DBUtils.getConn();
        String sql = "select item.rid,item.order_id,item.count,item.price," +
                "tb_order.sta,book.* from tb_order_item item " +
                "inner join tb_book book on item.product=book.isbn " +
                "inner join tb_order on item.order_id=tb_order.order_id " +
                "where item.order_id=? order by item.order_id desc,item.rid";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet =null;
            try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,orderId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
            book = new Book();
            book.setAuthor(resultSet.getString("author"));
            book.setEdition(resultSet.getInt("edition"));
            book.setForm(resultSet.getString("form"));
            book.setFormat(resultSet.getString("format"));
            book.setIsbn(resultSet.getString("isbn"));
            book.setPackaging(resultSet.getString("packaging"));
            book.setPages(resultSet.getInt("pages"));
            book.setPress(resultSet.getString("press"));
            book.setPrice(resultSet.getDouble("price"));
            book.setPublished(resultSet.getDate("published"));
            book.setTitle(resultSet.getString("title"));
            book.setWords(resultSet.getInt("words"));
            order = new Order();
            order.setSta(resultSet.getString("sta"));
            orderItem = new OrderItem();
            orderItem.setRid(resultSet.getInt("rid"));
            orderItem.setOrderId(resultSet.getString("order_id"));
            orderItem.setPrice(resultSet.getDouble("price"));
            orderItem.setCount(resultSet.getInt("count"));
            orderItemVo = new OrderItemVo();
            orderItemVo.setBook(book);
            orderItemVo.setOrderItem(orderItem);
            orderItemVo.setOrder(order);
            list.add(orderItemVo);
            }

        } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                if (resultSet!=null){
                    try {
                        resultSet.close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
                DBUtils.close(preparedStatement,connection);
            }
        return list;
    }
    public int selectCountByUser(User user) {
        int totalCount = 0;
        Connection con = DBUtils.getConn();
        String sql = "select count(*) "
                + "from tb_order_item item "
                + "inner join tb_book book "
                + "on item.product=book.isbn "
                + "inner join tb_order "
                + "on item.order_id=tb_order.order_id "
                + "where tb_order.user_id=?";
        PreparedStatement sta = null;
        ResultSet res = null;
        try {
            sta = con.prepareStatement(sql);
            sta.setString(1, user.getPhone());
            res = sta.executeQuery();
            if(res.next()){
                totalCount = res.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            if(res != null){
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            DBUtils.close(sta, con);
        }
        return totalCount;
    }
}
